package com.codrim.cs.data.server.dao.mapper;


import com.codrim.cs.data.rpc.dto.param.BillInOutReportParam;
import com.codrim.cs.data.rpc.dto.param.OfferReportField;
import com.codrim.cs.data.rpc.dto.result.BillInOutReportDTO;
import com.codrim.cs.data.rpc.enums.OrderBy;
import com.codrim.cs.data.server.dao.entity.MergeDayStatCsGeneralDO;
import com.codrim.cs.data.server.dao.entity.StatCsAffAllDO;
import com.codrim.cs.data.server.dao.entity.StatCsGeneralDO;
import com.codrim.cs.data.server.dao.param.AffAllGroupByDayAndSymbolParam;
import com.codrim.cs.data.server.dao.param.AffAllParam;
import com.codrim.cs.data.server.dao.param.AffDataRoleParam;
import com.codrim.cs.data.server.dao.param.OrderByColumn;
import com.codrim.cs.data.server.dao.param.ReportParam;
import com.codrim.cs.data.server.dao.param.SelectColumnNameAndAlias;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Arrays;
import java.util.List;
import java.util.Optional;

import static com.codrim.cs.data.rpc.dto.param.AdvertiserReportParamDTO.AdvertiserOfferReportField;

/**
 * 方法签名中的 String zone & String timeDimesion是必须参数,其使用见下类
 *
 * @author Liang.Zhuge on 13/07/2017.
 * @see com.codrim.cs.data.server.dao.interceptor.ZoneTableInterceptor <p> Mybatis mapper for {@link StatCsGeneralDO}
 */
@Mapper
public interface StatCsGeneralMapper extends StatCsMapper<StatCsGeneralDO, StatCsGeneralDO.StatCsGeneralUniqueKey> {
    String TABLE_NAME = "e0000_day_stat_cs_general";

    String SELECT_COLUMNS = "id, sdate, symbol, advertiser_id advertiserId, " +
            "affiliate_id affiliateId, offer_id offerId, country_id countryId, " +
            "browser, sorce_id sorceId, sub1, sub2, sub3, sub4, sub5, click, uclick, gc, " +
            "conversion, aprice, pprice, caps, sc, " +
            "customer_id customerId, " +
            "adv_advertiser_manager_id advAdvertiserManagerId, " +
            "adv_affiliate_manager_id advAffiliateManagerId, " +
            "aff_advertiser_manager_id  affAdvertiserManagerId, "+
            "adv_advertiser_manager_department_id advAdvertiserManagerDepartmentId ," +
            "adv_affiliate_manager_department_id advAffiliateManagerDepartmentId, " +
            "aff_advertiser_manager_department_id affAdvertiserManagerDepartmentId, " +
            "single_access_price singleAccessPrice," +
            "single_put_price singlePutPrice," +
            "access_type accessType ";

    @Insert("insert into e0000_day_stat_cs_general (" +
            "sdate, symbol, advertiser_id, affiliate_id, " +
            "offer_id, country_id, browser, sorce_id, " +
            "sub1, sub2, sub3, sub4," +
            "sub5, click, uclick, gc, " +
            "conversion, aprice, pprice, caps," +
            "sc, anonymous, report_type, " +
            "customer_id, " +
            "adv_advertiser_manager_id, " +
            "adv_affiliate_manager_id, " +
            "aff_advertiser_manager_id, " +
            "adv_advertiser_manager_department_id, " +
            "adv_affiliate_manager_department_id, " +
            "aff_advertiser_manager_department_id," +
            "single_access_price, " +
            "single_put_price, " +
            "access_type" +
            ") values (" +
            "#{param.sdate}, #{param.symbol}, #{param.advertiserId}, #{param.affiliateId}, " +
            "#{param.offerId}, #{param.countryId}, #{param.browser}, #{param.sorceId}," +
            "#{param.sub1}, #{param.sub2}, #{param.sub3}, #{param.sub4}, " +
            "#{param.sub5}, #{param.click}, #{param.uclick}, #{param.gc}, " +
            "#{param.conversion}, #{param.aprice}, #{param.pprice}, #{param.caps}, " +
            "#{param.sc}, #{param.anonymous}, #{param.reportType}," +
            "#{param.customerId}, " +
            "#{param.advAdvertiserManagerId}, " +
            "#{param.advAffiliateManagerId}, " +
            "#{param.affAdvertiserManagerId}, " +
            "#{param.advAdvertiserManagerDepartmentId}, " +
            "#{param.advAffiliateManagerDepartmentId}, " +
            "#{param.affAdvertiserManagerDepartmentId}, " +
            "#{param.singleAccessPrice}, " +
            "#{param.singlePutPrice}," +
            "#{param.accessType} " +
            ")")
    @Override
    void insert(@Param("param") StatCsGeneralDO param,
            @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    @Update("update e0000_day_stat_cs_general set " +
            "symbol = #{param.symbol}, " +
            "country_id = #{param.countryId}, " +
            "sorce_id = #{param.sorceId}, " +
            "sub1 = #{param.sub1}, " +
            "sub2 = #{param.sub2}, " +
            "sub3 = #{param.sub3}, " +
            "sub4 = #{param.sub4}, " +
            "sub5 = #{param.sub5}, " +
            "click = #{param.click}, " +
            "uclick = #{param.uclick}, " +
            "gc = #{param.gc}, " +
            "conversion = #{param.conversion}, " +
            "aprice = #{param.aprice}, " +
            "pprice = #{param.pprice}, " +
            "caps = #{param.caps}, " +
            "sc = #{param.sc}, " +
            "report_type = #{param.reportType}, " +
            "anonymous = #{param.anonymous}," +
            "customer_id = #{param.customerId}, " +
            "adv_advertiser_manager_id = #{param.advAdvertiserManagerId}," +
            "adv_affiliate_manager_id = #{param.advAffiliateManagerId}, " +
            "aff_advertiser_manager_id = #{param.affAdvertiserManagerId}, " +
            "adv_advertiser_manager_department_id = #{param.advAdvertiserManagerDepartmentId}," +
            "adv_affiliate_manager_department_id = #{param.advAffiliateManagerDepartmentId}, " +
            "aff_advertiser_manager_department_id = #{param.affAdvertiserManagerDepartmentId} " +
            "where offer_id = #{param.offerId} and affiliate_id = #{param.affiliateId} and sdate = #{param.sdate} "
            + "and report_type = #{param.reportType} and anonymous = #{param.anonymous}")
    void update(@Param("param") StatCsGeneralDO param, @Param("sZone") String zone);

    @Select("<script>select symbol,anonymous," +
            "<foreach item='item' index='index' collection='selectColumnNameAndAliases' separator=','>" +
            "${item.columnName} ${item.alias} " +
            "</foreach>" +
            "from " + TABLE_NAME +
            " where sdate between #{param.startDate} and #{param.endDate} " +
            "and report_type = #{param.reportType} " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.ADVERTISER + "' > " +
            "and advertiser_id = #{param.companyId} " +
            "</if> " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.AFFILIATE + "' > " +
            "and affiliate_id = #{param.companyId} " +
            "</if> " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +
            "<if test='param.affAdvertiserManagerIdList != null and param.affAdvertiserManagerIdList.size()!=0'>" +
            "and aff_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.affAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +

            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0'>" +
            "and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0'>" +
            "and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0'>" +
            " and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.accessType != null' > " +
            "and access_type = #{param.accessType} " +
            "</if> " +
            "group by symbol, anonymous " +
            "<foreach item='item' index='index' collection='groupByColumns' separator=''>" +
            ", ${item}" +
            "</foreach>" +
            "<if test='orderByColumns != null and orderByColumns.size() != 0'>" +
            " order by " +
            "<foreach item='item' index='index' collection='orderByColumns' open='' separator=',' close=''>" +
            " ${item.alias} ${item.orderByStr}" +
            "</foreach>" +
            "</if>" +
            " limit ${param.fromLine},${param.rows} " +
            "</script>")
    List<MergeDayStatCsGeneralDO> mergeDataFromDayStatCsGeneral(@Param("param") ReportParam param,
            @Param("selectColumnNameAndAliases") List<SelectColumnNameAndAlias> selectColumnNameAndAliases,
            @Param("groupByColumns") List<String> groupByColumns,
            @Param("orderByColumns") List<OrderByColumn> orderByColumns,
            @Param("sZone") String zone,
            @Param("timeDimesion") String timeDimesion);

    @Select("<script> select " +
            " symbol,"+
            " sum(click) clicks,sum(uclick) uniqueClicks,sum(gc) grossConversions,sum(aprice) revenue,sum(pprice) cost, " +
            " sum(conversion) conversions,sum(sc) scrubbedConversions,sum(caps) caps " +
            " from " + TABLE_NAME +
            " where sdate between #{param.startDate} and #{param.endDate} " +
            " and report_type = #{param.reportType} " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.ADVERTISER + "' > " +
            "and advertiser_id = #{param.companyId} " +
            "</if> " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.AFFILIATE + "' > " +
            " and affiliate_id = #{param.companyId} " +
            "</if> " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +
            "<if test='param.affAdvertiserManagerIdList != null and param.affAdvertiserManagerIdList.size()!=0'>" +
            "and aff_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.affAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +

            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
                "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
                "#{item}" +
                "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
                "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
                "#{item}" +
                "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0'>" +
            " and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0'>" +
            " and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0'>" +
            " and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affDepartmentIds != null and param.affDepartmentIds.size() != 0'>"+
            " and aff_advertiser_manager_department_id in "+
            "<foreach item='item' index='index' collection='param.affDepartmentIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>"+
            "<if test='param.affAdvertiserManagerId != null and param.affAdvertiserManagerId != 0'>"+
            " and aff_advertiser_manager_id = #{param.affAdvertiserManagerId}"+
            "</if>"+
            "<if test='param.accessType != null'>" +
            " and access_type = #{param.accessType} " +
            "</if>" +
            "GROUP BY symbol"+
            "</script>")
    List<MergeDayStatCsGeneralDO> sumDayStatCsGeneral(@Param("param") ReportParam param, @Param("sZone") String zone,
            @Param("timeDimesion") String timeDimesion);

    @Select("<script>" +
            "select count(1) from (" +
            "select symbol from " + TABLE_NAME +
            " where sdate between #{param.startDate} and #{param.endDate} " +
            "and report_type = #{param.reportType} " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.ADVERTISER + "' > " +
            "and advertiser_id = #{param.companyId} " +
            "</if> " +
            "<if test='param.dataOwner == " + ReportParam.DataOwner.AFFILIATE + "' > " +
            "and affiliate_id = #{param.companyId} " +
            "</if> " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +
            "<if test='param.affAdvertiserManagerIdList != null and param.affAdvertiserManagerIdList.size()!=0'>" +
            "and aff_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.affAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +

            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0'>" +
            " and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0'>" +
            "and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0'>" +
            " and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affDepartmentIds != null and param.affDepartmentIds.size() != 0'>"+
            " and aff_advertiser_manager_department_id in "+
            "<foreach item='item' index='index' collection='param.affDepartmentIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>"+
            "<if test='param.affAdvertiserManagerId != null and param.affAdvertiserManagerId != 0'>"+
            "and aff_advertiser_manager_id = #{param.affAdvertiserManagerId}"+
            "</if>"+

            "<if test='param.accessType != null'>" +
            "and access_type = #{param.accessType}" +
            "</if>" +

            "group by symbol, anonymous " +
            "<foreach item='item' index='index' collection='groupByColumns' separator=''>" +
            ", ${item}" +
            "</foreach>" +
            " ) tmp" +
            "</script>")
    int countDayStatCsGeneral(@Param("param") ReportParam param, @Param("groupByColumns") List<String> groupColumns,
            @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);


    enum AdvertiserMergeColumnAndAlias {
        Sdate(AdvertiserOfferReportField.Sdate, "sdate", "sdates"),
        Affiliate(AdvertiserOfferReportField.Affiliate, "affiliate_id", "affiliateId"),
        Offer(AdvertiserOfferReportField.Offer, "offer_id", "offerId"),
        Customer(AdvertiserOfferReportField.Customer, "customer_id", "customerId"),
        AdvAffiliateManager(AdvertiserOfferReportField.AdvAffiliateManager, "adv_affiliate_manager_id", "advAffiliateManagerId"),
        AdvAdvertiserManager(AdvertiserOfferReportField.AdvAdvertiserManager, "adv_advertiser_manager_id", "advAdvertiserManagerId"),
        AffiliateSource(AdvertiserOfferReportField.AffiliateSource, "sorce_id", "sourceId"),
        Sub1(AdvertiserOfferReportField.Sub1, "sub1", "sub1"),
        Sub2(AdvertiserOfferReportField.Sub2, "sub2", "sub2"),
        Sub3(AdvertiserOfferReportField.Sub3, "sub3", "sub3"),
        Sub4(AdvertiserOfferReportField.Sub4, "sub4", "sub4"),
        Sub5(AdvertiserOfferReportField.Sub5, "sub5", "sub5"),
        Clicks(AdvertiserOfferReportField.Clicks, "sum(click)", "clicks"),
        UniqueClicks(AdvertiserOfferReportField.UniqueClicks, "sum(uclick)", "uniqueClicks"),
        GrossConversions(AdvertiserOfferReportField.GrossConversions, "sum(gc)", "grossConversions"),
        Revenue(AdvertiserOfferReportField.Revenue, "sum(aprice)", "revenue"),
        Cost(AdvertiserOfferReportField.Cost, "sum(pprice)", "cost"),
        Conversions(AdvertiserOfferReportField.Conversions, "sum(conversion)", "conversions"),
        ScrubbedConversions(AdvertiserOfferReportField.ScrubbedConversions, "sum(sc)", "scrubbedConversions"),
        Caps(AdvertiserOfferReportField.Caps, "sum(caps)", "caps"),
        AccessType(AdvertiserOfferReportField.AccessType, "access_type", "accessType");

        private final AdvertiserOfferReportField field;
        private final SelectColumnNameAndAlias selectColumnNameAndAlias;

        public static String groupBy(OfferReportField field) {
            if (field == null) {
                return null;
            }
            Optional<AdvertiserMergeColumnAndAlias> first = Arrays.stream(AdvertiserMergeColumnAndAlias.values())
                    .filter(item -> field.isGroupBy() && item.field == field.getField()).findFirst();
            return first.isPresent() ? first.get().selectColumnNameAndAlias.columnName : null;
        }

        public static OrderByColumn orderBy(OfferReportField field) {
            if (field == null) {
                return null;
            }
            Optional<AdvertiserMergeColumnAndAlias> first = Arrays.stream(AdvertiserMergeColumnAndAlias.values())
                    .filter(item -> field.getOrderBy().getKey() != OrderBy.IGNORE && item.field == field.getField()).findFirst();
            return first.isPresent() ?
                   new OrderByColumn(first.get().selectColumnNameAndAlias.alias, field.getOrderBy().getDesc()) : null;
        }

        public static SelectColumnNameAndAlias select(OfferReportField field) {
            if (field == null || !field.isSelect()) {
                return null;
            }
            Optional<AdvertiserMergeColumnAndAlias> first = Arrays.stream(AdvertiserMergeColumnAndAlias.values())
                    .filter(item -> (item.field == field.getField())).findFirst();
            return first.isPresent() ? first.get().selectColumnNameAndAlias : null;
        }

        AdvertiserMergeColumnAndAlias(AdvertiserOfferReportField field, String columnName, String alias) {
            this.field = field;
            this.selectColumnNameAndAlias = new SelectColumnNameAndAlias(columnName, alias);
        }

        public SelectColumnNameAndAlias getSelectColumnNameAndAlias() {
            return this.selectColumnNameAndAlias;
        }
    }

    @Delete("delete from e0000_day_stat_cs_general where sdate = ${sdate}")
    void deleteSpecifiedDayReportData(@Param("sdate") int sdate, @Param("sZone") String zone);

    @Override
    @Select("<script> SELECT " + SELECT_COLUMNS +
            "from e0000_day_stat_cs_general " +
            "WHERE sdate = #{param.sdate} " +
            "<choose>" +
            "<when test='param.offerId !=null'> AND offer_id = #{param.offerId} </when>" +
            "<otherwise> AND offer_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advertiserId !=null'> AND advertiser_id = #{param.advertiserId} </when>" +
            "<otherwise> AND advertiser_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.affiliateId !=null'> AND affiliate_id = #{param.affiliateId} </when>" +
            "<otherwise> AND affiliate_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.sorceId !=null'> AND sorce_id = #{param.sorceId} </when>" +
            "<otherwise> AND sorce_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.symbol !=null'> AND symbol = #{param.symbol} </when>" +
            "<otherwise> AND symbol IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.countryId !=null'> AND country_id = #{param.countryId} </when>" +
            "<otherwise> AND country_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.reportType !=null'> AND report_type = #{param.reportType} </when>" +
            "<otherwise> AND report_type IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.anonymous !=null'> AND anonymous = #{param.anonymous} </when>" +
            "<otherwise> AND anonymous IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.customerId !=null'> AND customer_id = #{param.customerId} </when>" +
            "<otherwise> AND customer_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAdvertiserManagerId !=null'> AND adv_advertiser_manager_id = #{param.advAdvertiserManagerId} </when>" +
            "<otherwise> AND adv_advertiser_manager_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAffiliateManagerId !=null'> AND adv_affiliate_manager_id = #{param.advAffiliateManagerId} </when>" +
            "<otherwise> AND adv_affiliate_manager_id IS NULL </otherwise>" +
            "</choose>" +
            "<choose><when test='param.affAdvertiserManagerId !=null'> AND aff_advertiser_manager_id = #{param.affAdvertiserManagerId} </when>" +
            "<otherwise> AND aff_advertiser_manager_id IS NULL </otherwise></choose> " +
            "<choose><when test='param.singleAccessPrice !=null'> AND single_access_price = #{param.singleAccessPrice} </when>" +
            "<otherwise> AND single_access_price = 0 </otherwise></choose> " +
            "<choose>" +
            "<when test='param.accessType != null'> AND access_type = #{param.accessType} </when>" +
            "<otherwise> AND access_type IS NULL </otherwise>" +
            "</choose> " +
            "<choose><when test='param.singlePutPrice !=null'> AND single_put_price = #{param.singlePutPrice} </when>" +
            "<otherwise> AND single_put_price = 0 </otherwise></choose> " +
            "LIMIT 1 </script>")
    StatCsGeneralDO findByKey(@Param("param") StatCsGeneralDO.StatCsGeneralUniqueKey key,
            @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    @Update("update e0000_day_stat_cs_general set " +
            "click = #{values.click}, " +
            "uclick = #{values.uclick}, " +
            "gc = #{values.gc}, " +
            "conversion = #{values.conversion}, " +
            "aprice = #{values.aprice}, " +
            "pprice = #{values.pprice}, " +
            "caps = #{values.caps}, " +
            "sc = #{values.sc} " +
            "where id = #{id}")
    @Override
    void updateById(@Param("values") StatCsGeneralDO dayStatCsGeneralDO,
            @Param("id") Integer id, @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    @Select("<script>select " +
            "( sdate DIV 100 )         AS sdate,id,sdate,customer_id AS advertiserId,affiliate_id AS affiliateId,offer_id AS offerId,sum(conversion) AS conversion," +
            "adv_advertiser_manager_id AS advAdvertiserManagerId,adv_affiliate_manager_id AS advAffiliateManagerId,aff_advertiser_manager_id AS affAdvertiserManagerId," +
            "sum(aprice) AS aprice,sum(pprice) AS pprice,symbol"
            + " from " + TABLE_NAME +
            "<where>" +
            " advertiser_id=#{param.advertiserId}" +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            " and sdate between #{param.startTime} and #{param.endTime}" +
            "</if>" +
            "<if test='param.offerId!=null'>" +
            " and offer_id = #{param.offerId}" +
            "</if>" +
            "<if test='param.affiliateId!=null'>" +
            " and affiliate_id = #{param.affiliateId}" +
            "</if>" +
            "<if test='param.affManagerId!=null'>" +
            " and adv_affiliate_manager_id = #{param.affManagerId}" +
            "</if>" +
            "<if test='param.advManagerId!=null'>" +
            " and adv_advertiser_manager_id = #{param.advManagerId}" +
            "</if>" +
            "<if test='param.customerId!=null'>"+
            " and customer_id = #{param.customerId}"+
            "</if>"+
            "<if test='param.affiliateId!=null'>"+
            " and affiliate_id = #{param.affiliateId}"+
            "</if>"+
            "</where>" +
            " group by (sdate DIV 100), offer_id " +
            "order by sdate desc" +
            "<if test='param.page!=null and param.rows!=null'>" +
            " limit #{param.page},#{param.rows}" +
            "</if>" +
            "</script>")
    List<BillInOutReportDTO> findCsGeneralByParam(@Param("param") BillInOutReportParam param,
            @Param("sZone") String zone);

    @Select("<script>select count(1) from (select " +
            "*"
            + " from " + TABLE_NAME +
            "<where>" +
            " advertiser_id=#{param.advertiserId}" +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            " and sdate between #{param.startTime} and #{param.endTime}" +
            "</if>" +
            "<if test='param.offerId!=null'>" +
            " and offer_id = #{param.offerId}" +
            "</if>" +
            "<if test='param.affiliateId!=null'>" +
            " and affiliate_id = #{param.affiliateId}" +
            "</if>" +
            "<if test='param.affManagerId!=null'>" +
            " and adv_affiliate_manager_id = #{param.affManagerId}" +
            "</if>" +
            "<if test='param.advManagerId!=null'>" +
            " and adv_advertiser_manager_id = #{param.advManagerId}" +
            "</if>" +
            "<if test='param.customerId!=null'>"+
            " and customer_id = #{param.customerId}"+
            "</if>"+
            "<if test='param.affiliateId!=null'>"+
            " and affiliate_id = #{param.affiliateId}"+
            "</if>"+
            "</where> group by offer_id,( sdate DIV 100 )) a" +
            "</script>")
    Integer findCsGeneralByParamCount(@Param("param") BillInOutReportParam param,
            @Param("sZone") String zone);

    @Select("<script>"
            + "select "
            + "affiliate_id affiliateId, "
            + "sdate, "
            + "symbol, "
            + "sum(click) click, "
            + "sum(uclick) uclick, "
            + "sum(conversion) conversion, "
            + "sum(pprice) pprice "
            + " from " + TABLE_NAME
            + " where affiliate_id = #{param.companyId} "
            + " and sdate = #{param.date} "
            + "<if test='param.departmentIds != null and param.departmentIds.size() > 0'>"
            + " and aff_advertiser_manager_department_id in"
            + "    <foreach item='paramDeptId' index='index' collection='param.departmentIds' open='(' separator=',' close=')'>"
            + "       #{paramDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='param.advertiserManagerId != null'>"
            + " and aff_advertiser_manager_id = #{param.advertiserManagerId} "
            + "</if>"
            /* 权限 */
            + "<trim prefix=' and (' suffix=') ' prefixOverrides='OR'>"
            + "<if test='roleParam != null and roleParam.advertiserManagerDepartmentIds != null and roleParam.advertiserManagerDepartmentIds.size() > 0'>"
            + " OR aff_advertiser_manager_department_id in "
            + "    <foreach item='roleDeptId' index='index' collection='roleParam.advertiserManagerDepartmentIds' open=' (' separator=',' close=') '>"
            + "       #{roleDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='roleParam != null and roleParam.advertiserManagerId != null'>"
            + " OR aff_advertiser_manager_id = #{roleParam.advertiserManagerId} "
            + "</if>"
            + "</trim>"
            /* /权限 */
            + " group by affiliate_id, sdate, symbol"
            + "</script>")
    List<StatCsAffAllDO> affAll(@Param("param") AffAllParam param, @Param("roleParam") AffDataRoleParam roleParam,
            @Param("sZone") String zone);


    @Select("<script>"
            + "select "
            + "affiliate_id affiliateId, "
            + "sdate, "
            + "symbol, "
            + "sum(click) click, "
            + "sum(uclick) uclick, "
            + "sum(conversion) conversion, "
            + "sum(pprice) pprice "
            + " from " + TABLE_NAME
            + " where affiliate_id = #{param.companyId} "
            + " and sdate between #{param.begin} and #{param.end} "
            + "<if test='param.departmentIds != null and param.departmentIds.size() > 0'>"
            + " and aff_advertiser_manager_department_id in"
            + "    <foreach item='paramDeptId' index='index' collection='param.departmentIds' open='(' separator=',' close=')'>"
            + "       #{paramDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='param.advertiserManagerId != null'>"
            + " and aff_advertiser_manager_id = #{param.advertiserManagerId} "
            + "</if>"
            /* 权限 */
            + "<trim prefix=' and (' suffix=') ' prefixOverrides='OR'>"
            + "<if test='roleParam != null and roleParam.advertiserManagerDepartmentIds != null and roleParam.advertiserManagerDepartmentIds.size() > 0'>"
            + " OR aff_advertiser_manager_department_id in "
            + "    <foreach item='roleDeptId' index='index' collection='roleParam.advertiserManagerDepartmentIds' open=' (' separator=',' close=') '>"
            + "       #{roleDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='roleParam != null and roleParam.advertiserManagerId != null'>"
            + " OR aff_advertiser_manager_id = #{roleParam.advertiserManagerId} "
            + "</if>"
            + "</trim>"
            /* /权限 */
            + " group by sdate, symbol, affiliate_id "
            + "</script>")
    List<StatCsAffAllDO> affAllGroupByDayAndSymbol(@Param("param") AffAllGroupByDayAndSymbolParam param,
            @Param("roleParam") AffDataRoleParam roleParam, @Param("sZone") String zone,
            @Param("timeDimesion") String timeDimesion);
}
